package pos.data;
import java.util.*;
import pos.data.IServiceDAO;
import pos.data.JDBCServiceDAO;


import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import pos.domain.Exchange;
import pos.domain.Property;
import pos.domain.Service;
import pos.domain.User;
import pos.utils.UIDGenerator;


public class JDBCPropertyDAO implements IPropertyDAO{
	
	

	public void insert(Connection conn, String email,Property p, Service s) {
		
		  String sql = "INSERT INTO Property ( propertyOID,surface, type, address, population, country, rooms,capacity,calification,entryDate,owner, propertyID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?) ";
	        PreparedStatement stmt = null;
	      String propertyOID= UIDGenerator.getInstance().getKey();
	      p.setOwner(email);
	      IServiceDAO sdao=new JDBCServiceDAO();
	      
	        try {
	            stmt = conn.prepareStatement(sql);

	            stmt.setString(1, propertyOID);
	            stmt.setString(2, p.getSurface());
	            stmt.setString(3, p.getType());
	            stmt.setString(4, p.getAddress());
	            stmt.setString(5,p.getPopulation());
	            stmt.setString(6, p.getCountry());
	            stmt.setInt(7, p.getRooms());
	            stmt.setInt(8,p.getCapacity());
	            stmt.setDouble(9,0.0f);
	            stmt.setDate(10,(Date) p.getEntryDate());
	            stmt.setString(11,p.getOwner());
	            stmt.setString(12, p.getPropertyID());
	            
	            /*System.out.println("Property dentro de JDBC:");
	            System.out.println(propertyOID);
	            System.out.println( p.getSurface());
	            System.out.println( p.getType());
	            System.out.println( p.getAddress());
	            System.out.println(p.getPopulation());
	            System.out.println( p.getCountry());
	            System.out.println(p.getRooms());
	            System.out.println(p.getCapacity());
	            System.out.println(p.getCalification());
	            System.out.println( p.getEntryDate());
	            System.out.println(p.getOwner());
	            System.out.println( p.getPropertyID());
	            */
	            
	            
	            
	           int i=stmt.executeUpdate();
	            
	            System.out.println("rows en property:"+i);
	            //Asocio los servicios a la casa
		        
		        sdao.insert(conn,p.getPropertyID(),s);
	            
	            
	        } catch (SQLException e) {
	            System.out.println("Message: " + e.getMessage());
	            System.out.println("SQLState: " + e.getSQLState());
	            System.out.println("ErrorCode: " + e.getErrorCode());
	        } finally {
	            try {
	                if (stmt != null) {
	                    stmt.close();
	                }
	            } catch (SQLException e) {
	            }
	        }
	        
	   
		
	}

	public void delete(Connection conn, String propertyOID) {
		
		String sql= "DELETE FROM Property where (propertyID = ?)";
		PreparedStatement stmt = null;
		
		try {
			stmt=conn.prepareStatement(sql);
			
			stmt.setString(1,propertyOID);
			stmt.executeUpdate();
			
		 } catch (SQLException e) {
	            System.err.println("Message: " + e.getMessage());
	            System.err.println("SQLState: " + e.getSQLState());
	            System.err.println("ErrorCode: " + e.getErrorCode());
	        } finally {
	            try {
	                if (stmt != null)
	                    stmt.close();
	            	} catch (SQLException e) {}
	        }
		
	}

	public Property select(String pid) {
	
	    	 Connection conn = ConnectionManager.getInstance().checkOut();
	         PreparedStatement stmt = null;
	         ResultSet result = null;
	         Property p = new Property();
	         String sql = "SELECT * FROM Property WHERE (propertyID = ?) ";

	         try {
	             stmt = (PreparedStatement) conn.prepareStatement(sql);
	             stmt.setString(1, pid);
	             result = stmt.executeQuery();

	             result.next();
	             p.setOwner(result.getString("owner"));
	             p.setAddress(result.getString("address"));
	             p.setCountry(result.getString("country"));
	             p.setPopulation(result.getString("population"));
	             p.setType(result.getString("type"));

	          
	         } catch (SQLException e) {
	             System.out.println("Message: " + e.getMessage());
	             System.out.println("SQLState: " + e.getSQLState());
	             System.out.println("ErrorCode: " + e.getErrorCode());
	         } finally {
	        	
	        	 ConnectionManager.getInstance().checkIn(conn);
	             try {
	            	
	                 if (result != null) {
	                     result.close();
	                 }
	                 if (stmt != null) {
	                     stmt.close();
	                 }
	             } catch (SQLException e) {
	             }
	         }
	         return p;
	     }
	     
	
	public List<Property>  selectProperyts(String email) {
		
		Connection conn = ConnectionManager.getInstance().checkOut();

	    PreparedStatement stmt = null;
	    List<Property> myPropertys = new LinkedList<Property>();
	    ResultSet result = null;

	    try 
	    {

	        String sql = "SELECT * FROM Property WHERE (owner = ?)";
	        stmt = conn.prepareStatement(sql);
	        stmt.setString(1,email);
	        result = stmt.executeQuery();
	        
	        while (result.next())
	        {
	        	
	            Property p = new Property();
	            p.setPropertyID(result.getString("propertyID"));
	            p.setAddress(result.getString("address"));
	            p.setCountry(result.getString("country"));
	            
	           myPropertys.add(p);
	        }
	     }
	    catch (SQLException e)
	    	{
	            System.out.println("Message: " + e.getMessage());
	            System.out.println("SQLState: " + e.getSQLState());
	            System.out.println("ErrorCode: " + e.getErrorCode());
	        }
	    finally 
	    {
	            ConnectionManager.getInstance().checkIn(conn);
	            
	            try
	            {
	                if (result != null)
	                    result.close();
	                if (stmt != null)
	                    stmt.close();
	            }
	            catch (SQLException e)
	            {
	            }
	     }
	    return myPropertys;
	}


	
	
	 public List<Property> selectPropertyByFields(List<String> query,List<Boolean> services)
	 {
		 
		 /*String sql = "SELECT * FROM Property ";
		             sql=sql.concat("WHERE country="+query.get(0));
		         if(query.get(1)!=null)
		             sql=sql.concat(" AND population="+query.get(1));
		         if(query.get(2)!=null)
		             sql=sql.concat(" AND type="+query.get(2));
		         if(query.get(3)!=null)
		             sql=sql.concat(" AND capacity="+query.get(3));
		         if(query.get(4)!=null)
		             sql=sql.concat(" AND rooms="+query.get(4));
		         sql=sql.concat(";");
		    System.out.println(sql);
	       */ Connection conn = ConnectionManager.getInstance().checkOut();

	        PreparedStatement stmt = null;
	        List<Property> searchResults = new LinkedList<Property>();
	        ResultSet result = null;

	        try 
	        {
	            stmt = conn.prepareStatement("SELECT * FROM Property "); //conn es una conexi�n v�lida
	            result = stmt.executeQuery();
	            
	            while (result.next())
	            {
	                Property temp = new Property();
	                temp.setPropertyID(result.getString("propertyID"));
	                System.out.println("propertyID="+temp.getPropertyID());
	                temp.setAddress(result.getString("address"));
	                temp.setPopulation(result.getString("population"));
	                temp.setCountry(result.getString("country"));
	                temp.setOwner(result.getString("owner"));
	                temp.setType(result.getString("type"));
	                temp.setSurface(result.getString("surface"));
	                temp.setRooms(result.getInt("rooms"));
	                temp.setCapacity(result.getInt("capacity"));
	                temp.setCalification(result.getDouble("calification"));
	                temp.setEntryDate(result.getDate("entryDate"));
	                boolean existecasa=false;
	                if(temp.getCountry().equals(query.get(0)) && temp.getPopulation().equals(query.get(1)) && temp.getType().equals(query.get(2)) && temp.getCapacity().equals(Integer.parseInt(query.get(3)))&& temp.getRooms().equals(Integer.parseInt(query.get(4))))
	                	existecasa=true;
	                Service s = new JDBCServiceDAO().selectPropertyByFields(conn,services,result.getString("propertyID"));
	                if(existecasa && s!=null){
	                	temp.setServices(s);
	                	searchResults.add(temp);
	                	}
	            }
	         }
	        catch (SQLException e)
	        	{
	                System.out.println("Message: " + e.getMessage());
	                System.out.println("SQLState: " + e.getSQLState());
	                System.out.println("ErrorCode: " + e.getErrorCode());
	            }
	        finally 
	        {
	                ConnectionManager.getInstance().checkIn(conn);
	                
	                try
	                {
	                    if (result != null)
	                        result.close();
	                    if (stmt != null)
	                        stmt.close();
	                }
	                catch (SQLException e)
	                {
	                }
	         }
	        return searchResults;
	            }
	 

public List<Property> selectPropertyByService(List<Boolean> search){

{
	        Connection conn = ConnectionManager.getInstance().checkOut();

	        PreparedStatement stmt = null;
	        List<Property> searchResults = new LinkedList<Property>();
	        ResultSet result = null;

	        try 
	        {

	            String sql = "SELECT * FROM Property";
	            stmt = conn.prepareStatement(sql); //conn es una conexi�n v�lida
	            result = stmt.executeQuery();
	            
	            while (result.next())
	            {
	                
	                IServiceDAO jsd = new JDBCServiceDAO();
	                Service s=jsd.selectPropertyByFields(conn,search,result.getString("propertyID"));
	                if (s!=null){	
	                Property temp = new Property();
	                temp.setPropertyID(result.getString("propertyID"));
 	                temp.setAddress(result.getString("address"));
 	                temp.setPopulation(result.getString("population"));
 	                temp.setCountry(result.getString("country"));
 	                temp.setOwner(result.getString("owner"));
 	                temp.setType(result.getString("type"));
 	                temp.setSurface(result.getString("surface"));
 	                temp.setRooms(result.getInt("rooms"));
 	                temp.setCapacity(result.getInt("capacity"));
 	                temp.setCalification(result.getDouble("calification"));
 	                temp.setEntryDate(result.getDate("entryDate"));
	                temp.setServices(s);
	                searchResults.add(temp);
	                }	                
	            }
	         }
	        catch (SQLException e)
	        	{
	                System.out.println("Message: " + e.getMessage());
	                System.out.println("SQLState: " + e.getSQLState());
	                System.out.println("ErrorCode: " + e.getErrorCode());
	            }
	        finally 
	        {
	                ConnectionManager.getInstance().checkIn(conn);
	                
	                try
	                {
	                    if (result != null)
	                        result.close();
	                    if (stmt != null)
	                        stmt.close();
	                }
	                catch (SQLException e)
	                {
	                }
	         }
	        return searchResults;
	            }
	 
}
public List<Property> sortByNewestProperties (){
	Connection conn = ConnectionManager.getInstance().checkOut();

    PreparedStatement stmt = null;
    List<Property> searchResults = new LinkedList<Property>();
    ResultSet result = null;

    try 
    {

        String sql = "SELECT * FROM Property ORDER BY entryDate ASC";
        stmt = conn.prepareStatement(sql); //conn es una conexi�n v�lida
        result = stmt.executeQuery();

        while (result.next()&&(!(searchResults.size()<21)))
        {
            Property temp = new Property();
            temp.setPropertyID(result.getString("propertyID"));
            temp.setAddress(result.getString("address"));
            temp.setPopulation(result.getString("population"));
            temp.setCountry(result.getString("country"));
            temp.setOwner(result.getString("owner"));
            temp.setType(result.getString("type"));
            temp.setSurface(result.getString("surface"));
            temp.setRooms(result.getInt("rooms"));
            temp.setCapacity(result.getInt("capacity"));
            temp.setCalification(result.getDouble("calification"));
            temp.setEntryDate(result.getDate("entryDate"));
            searchResults.add(temp);
        }
     }
    catch (SQLException e)
    	{
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        }
    finally 
    {
            ConnectionManager.getInstance().checkIn(conn);
            
            try
            {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            }
            catch (SQLException e)
            {
            }
     }
    return searchResults;
}
public List<Property> sortByTopRatedProperties (){
	Connection conn = ConnectionManager.getInstance().checkOut();

    PreparedStatement stmt = null;
    List<Property> searchResults = new LinkedList<Property>();
    ResultSet result = null;

    try 
    {

        String sql = "SELECT * FROM Property ORDER BY calification ASC";
        stmt = conn.prepareStatement(sql); //conn es una conexi�n v�lida
        result = stmt.executeQuery();
        
        while (result.next()&&(!(searchResults.size()<21)))
        {
            Property temp = new Property();
            temp.setPropertyID(result.getString("propertyID"));
            temp.setAddress(result.getString("address"));
            temp.setPopulation(result.getString("population"));
            temp.setCountry(result.getString("country"));
            temp.setOwner(result.getString("owner"));
            temp.setType(result.getString("type"));
            temp.setSurface(result.getString("surface"));
            temp.setRooms(result.getInt("rooms"));
            temp.setCapacity(result.getInt("capacity"));
            temp.setCalification(result.getDouble("calification"));
            temp.setEntryDate(result.getDate("entryDate"));
            searchResults.add(temp);
        }
     }
    catch (SQLException e)
    	{
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        }
    finally 
    {
            ConnectionManager.getInstance().checkIn(conn);
            
            try
            {
                if (result != null)
                    result.close();
                if (stmt != null)
                    stmt.close();
            }
            catch (SQLException e)
            {
            }
     }
    return searchResults;
}








}
	
	
